<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of course_model
 *
 * @author Eko Prasetyo<nir@nirleka.web.id>
 */
class course_model extends CI_Model {

	public function __construct() {
		parent::__construct();
	}

	/**
	 * Mengambil semua data course yang sudah berjalan
	 * @return array of course
	 */
	function get_all_course() {
		$data = $this->db
				->select('kode_pelatihan, nama_pelatihan, angkatan')
				->distinct()
				->from('pelatihan natural join kelas')
				->order_by("kode_pelatihan", "asc")
				->order_by("angkatan", "asc")
				->where('status', 'JALAN')
				->get()
				->result_array();
		$i = 0;
		foreach ($data as $row) {
			$data[$i]['jum_pelatihan'] = $this->get_count_pelatihan($row['kode_pelatihan'], $row['angkatan']);
			$data[$i]['pelatihan_selesai'] = $this->get_count_pelatihan_selesai($row['kode_pelatihan'], $row['angkatan']);
			$i++;
		}
		return $data;
	}

	/**
	 * Mengambil semua data course yang sudah ada jadwalnya
	 * @return array kode_pelatihan
	 */
	function get_course_for_role($exclude = array(), $mode = '-') {

		$data = array();
		if (!empty($exclude)) {
			$ex_kode_pelatihan = array();
			$ex_tahun = array();
			$ex_term = array();
			foreach ($exclude as $row) {
				$temp = explode('-', $row);
				array_push($ex_kode_pelatihan, $temp[0]);
				array_push($ex_tahun, $temp[1]);
				array_push($ex_term, $temp[2]);
			}
			$tmp = $this->db
					->distinct()
					->select("kode_pelatihan, nama_pelatihan")
					->from("pelatihan natural join kelas")
					->or_where_not_in('kode_pelatihan', $ex_kode_pelatihan)
					->or_where_not_in('tahun', $ex_tahun)
					->or_where_not_in('term', $ex_term)
					->where('status', 'JALAN')
					->order_by("kode_pelatihan")
					->order_by("angkatan")
					->get()
					->result_array();
		} else {
			$tmp = $this->db
					->distinct()
					->select("kode_pelatihan, nama_pelatihan")
					->from("pelatihan natural join kelas")
					->not_like('status', 'SELESAI')
					->order_by("kode_pelatihan")
					->order_by("angkatan")
					->get()
					->result_array();
		}
		if ($mode == '-')
			$data['-'] = '-';
		for ($i = 0; $i < count($tmp); $i++) {
			$data[$tmp[$i]['kode_pelatihan']] = $tmp[$i]['kode_pelatihan'] . ' - ' . $tmp[$i]['nama_pelatihan'];
		}
		return $data;
	}

	/**
	 * Mendapatkan data dari tabel mengajar_kelas
	 * @param String $kode_pelatihan
	 * @param String $field
	 * @return array data dari field
	 */
	function get_mengajar_kelas($kode_pelatihan, &$roles = array()) {
		$data = array();
		$tmp = $this->db
				->select('tahun, term')
				->distinct()
				->from('kelas')
				->where('kode_pelatihan', $kode_pelatihan)
				->get()
				->result_array();
		for ($i = 0; $i < count($tmp); $i++) {
			$data[$i]['data'] = $tmp[$i]['tahun'] . '-' . $tmp[$i]['term'];
			$data[$i]['tahun'] = $tmp[$i]['tahun'];
			$data[$i]['term'] = $tmp[$i]['term'];
			$tmp_status = $this->db
					->select('role')
					->from("request_role")
					->where('tahun', $tmp[$i]['tahun'])
					->where('term', $tmp[$i]['term'])
					->where('id_user', $this->session->userdata('id'))
					->where('kode_pelatihan', $kode_pelatihan)
					->get();
			if ($tmp_status->num_rows() > 0)
				$data[$i]['selected'] = true;
			else
				$data[$i]['selected'] = false;
		}
		return $data;
	}

	/**
	 * Mendapatkan id_role dan nama_role dari table role
	 * @return array daftar role
	 */
	function get_role() {
		$data = array();
		$tmp = $this->db
				->select('id_role, nama_role')
				->from('daftar_role')
				->get()
				->result_array();
		for ($i = 0; $i < count($tmp); $i++) {
			$data[$i]['values'] = $tmp[$i]['id_role'];
			$data[$i]['text'] = $tmp[$i]['nama_role'];
		}
		return $data;
	}

	function is_role_exist($field, $value, $kode_pelatihan) {
		$tmp = $this->db
				->select('id')
				->from('mengajar_kelas')
				->where('id', $this->session->userdata('id'))
				->where('kode_pelatihan', $kode_pelatihan)
				->where($field, $value)
				->get();
		if ($tmp->num_rows != 0)
			return true;
		else
			return false;
	}

	/**
	 * Menghitung total course yang disediakan sesuai parameter $kode
	 * @param String $kode Kode course yang ingin dihitung banyaknya
	 * @return integer
	 */
	function get_count_pelatihan($kode, $angkatan) {
		$data = $this->db
				->select('jam_total, jam_per_sesi')
				->from('kelas')
				->where('kode_pelatihan', $kode)
				->where('status', 'JALAN')
				->where('angkatan', $angkatan)
				->get()
				->row_array();
		if (!empty($data))
			return $data['jam_total'] / $data['jam_per_sesi'];
		else
			return NULL;
	}

	/**
	 * Mengembalikan jumlah course yang telah selesai
	 * @param String $kode Kode Course
	 * @return integer jumlah pelatihan telah selesai
	 */
	function get_count_pelatihan_selesai($kode, $angkatan) {
		$data = $this->db
				->select('kode_pelatihan')
				->from('daftar_jadwal')
				->where('kode_pelatihan', $kode)
				->where('angkatan', $angkatan)
				->where('tanggal <= ', date("Y-m-d", strtotime("now")))
				->count_all_results();
		if (!empty($data))
			return $data;
		else
			return NULL;
	}

	/**
	 * Mengembalikan list course yang
	 * @param String $kode_pelatihan
	 * @param String $angkatan
	 * @return ArrayList dari suatu course beserta jadwalnya, trainernya, <br/>
	 * 		   dan daftar sesinya sesuai parameter kode pelatihan dan angkatannya
	 */
	public function get_courses($kode_pelatihan, $angkatan) {
		//inisialisasi course
		$courses = array(
			'courses_name' => $this->db
					->select('kode_pelatihan, nama_pelatihan, angkatan, jenis')
					->distinct()
					->from('pelatihan p natural join kelas')
					->where('kode_pelatihan', $kode_pelatihan)
					->where('angkatan', $angkatan)
					->order_by("kode_pelatihan", "asc")
					->get()
					->row_array(),
			'courses_schedule' => array(),
			'trainer_list' => $this->db
					->select("nama")
					->distinct()
					->from('user natural join ajar')
					->where('kode_pelatihan', $kode_pelatihan)
					->where('angkatan', $angkatan)
					->order_by('nama', 'asc')
					->get()
					->result_array(),
			'courses_list' => $this->db
					->select('id_ruangan, dj.angkatan, nama_ruangan, tanggal, sp.kode_pelatihan, dj.nomor_sesi, judul_sesi, jam_mulai, jam_selesai')
					->distinct()
					->from('daftar_jadwal dj natural join sesi_pelatihan sp
											inner join daftar_ruangan using(id_ruangan)')
					->where('dj.kode_pelatihan', $kode_pelatihan)
					->where('dj.angkatan', $angkatan)
					->order_by('nomor_sesi', 'asc')
					->get()
					->result_array(),
			'next_status' => false,
			'jum_pelatihan' => $this->get_count_pelatihan($kode_pelatihan, $angkatan),
			'pelatihan_selesai' => $this->get_count_pelatihan_selesai($kode_pelatihan, $angkatan)
		);

		if ($courses['courses_name'] != NULL) {
			//ambil jadwal dengan waktu min dan max (waktu yg bisa untuk training)
			$temp_course = $this->db
					->select('hari, nama_ruangan, kode_waktu')
					->from("jadwal_pelatihan jp natural join daftar_ruangan")
					->where('jp.kode_pelatihan', $kode_pelatihan)
					->where('jp.angkatan', $angkatan)
					->order_by('jp.hari')
					->order_by('jp.kode_waktu')
					->get()
					->result_array();
			if (!empty($temp_course)) {
				$min = $temp_course[0];
				$max = $temp_course[0]['kode_waktu'];
				$j = 0;
				for ($i = 1; $i < count($temp_course); $i++) {
					if ($max < intval($temp_course[$i]['kode_waktu'])) {
						$max = $temp_course[$i]['kode_waktu'];
					}
					if (((strcmp($min['hari'], $temp_course[$i]['hari']) != 0) &&
							(strcmp($min['kode_waktu'], $temp_course[$i]['kode_waktu']) != 0)) ||
							($i == count($temp_course) - 1)) {
						$temp['nama_ruangan'] = $min['nama_ruangan'];
						$temp['hari'] = $min['hari'];
						$temp['course_min_time'] = $min['kode_waktu'];
						$temp['course_max_time'] = $max;
						$courses['courses_schedule'][$j] = $temp;
						$j++;
						$min = $temp_course[$i - 1];
						$max = $temp_course[$i]['kode_waktu'];
					}
				}

				//ambil index next course
				$i = 0;
				$today = new DateTime(date("Y-m-d", strtotime("now")));
				foreach ($courses['courses_list'] as $row) {
					$compare = new DateTime(date("Y-m-d", strtotime($row['tanggal'])));
					if ($compare >= $today) {
						$courses['next'] = $i;
						$courses['next_status'] = true;
						break;
					}
					$i++;
				}
			}
			//status course passed | today | incomming
			$i = 0;
			foreach ($courses['courses_list'] as $value) {
				$course_time = $value['tanggal'] . " " . $value['jam_mulai'];
				$courses['courses_list'][$i]['status'] = $this->course_status($course_time);
				$i++;
			}
			//get pelatih
			$i = 0;
			foreach ($courses['courses_list'] as $jadwal) {
				$tmp_pelatih = $this->db
						->select('nama')
						->from('sesi_pelatihan natural join ajar aj natural join user')
						->where('aj.kode_pelatihan', $jadwal['kode_pelatihan'])
						->where('aj.nomor_sesi', $jadwal['nomor_sesi'])
						->where('aj.angkatan', $angkatan)
						->order_by('id')
						->get()
						->result_array();
				$courses['courses_list'][$i]['list_pengajar'] = $tmp_pelatih;
				$i++;
			}
			return $courses;
		} else {
			return NULL;
		}
	}

	/**
	 * Fungsi mengambil data course dan pengajar menurut waktu
	 * @param String $time menggunakan bahasa inggri: contoh "now", "+ 24 hours". Lihat fungsi date()
	 * @return list daftar course pada hari tersebut
	 */
	public function get_course_byday($time = "now") {
		$time = date("Y-m-d", strtotime($time));
		$kode_ruangan = $this->db
				->select('id_ruangan, nama_ruangan')
				->order_by('nama_ruangan', "asc")
				->get('daftar_ruangan')
				->result_array();
		$i = 0;
		foreach ($kode_ruangan as $room) {
			//cari jadwal per ruangan
			$tmp_jadwal = array();
			$tmp_pelatih = array();
			$tmp_jadwal = $this->db
					->select('tanggal, dj.kode_pelatihan, sp.nomor_sesi, sp.angkatan, judul_sesi, jam_mulai, jam_selesai')
					->from('daftar_jadwal dj right join sesi_pelatihan sp on(dj.kode_pelatihan = sp.kode_pelatihan)')
					->distinct()
					->where('id_ruangan', $room['id_ruangan'])
					->where('tanggal', $time)
					->order_by('nomor_sesi', 'asc')
					->get()
					->result_array();
			$kode_ruangan[$i]['list_jadwal'] = $tmp_jadwal;
			$j = 0;
			foreach ($tmp_jadwal as $jadwal) {
				//cari pelatih per ruangan
				$tmp_pelatih = $this->db
						->select('nama')
						->from('sesi_pelatihan natural join ajar aj natural join user')
						->where('aj.kode_pelatihan', $jadwal['kode_pelatihan'])
						->where('aj.nomor_sesi', $jadwal['nomor_sesi'])
						->where('aj.angkatan', $jadwal['angkatan'])
						->order_by('id')
						->get()
						->result_array();
				$kode_ruangan[$i]['list_jadwal'][$j]['list_pengajar'] = $tmp_pelatih;
				$j++;
			}
			$i++;
		}
		return $kode_ruangan;
	}

	public function course_status($time) {
		$today = new DateTime(date("Y-m-d", strtotime("now")));
		$compare = new DateTime(date("Y-m-d", strtotime($time)));

		if ($today == $compare)
			return "today";
		else if ($today > $compare)
			return "passed";
		else if ($today < $compare)
			return "incoming";
	}

	public function decode_date($code) {
		if ($code == 1)
			return "Monday";
		else if ($code == 2)
			return "Tuesday";
		else if ($code == 3)
			return "Wednesday";
		else if ($code == 4)
			return "Thursday";
		else if ($code == 5)
			return "Friday";
		else if ($code == 6)
			return "Saturday";
		else
			return "Sunday";
	}

	public function decode_time_min($param) {
		if ($param == 1)
			return "15.00";
		else if ($param == 2)
			return "15.30";
		else if ($param == 3)
			return "16.00";
		else if ($param == 4)
			return "16.30";
		else if ($param == 5)
			return "17.00";
		else if ($param == 6)
			return "17.30";
		else if ($param == 7)
			return "18.00";
		else if ($param == 8)
			return "18.30";
		else if ($param == 9)
			return "19.00";
		else if ($param == 10)
			return "19.30";
		else if ($param == 11)
			return "20.00";
		else if ($param == 12)
			return "20.30";
	}

	public function decode_time_max($param) {
		if ($param == 1)
			return "15.30";
		else if ($param == 2)
			return "16.00";
		else if ($param == 3)
			return "16.30";
		else if ($param == 4)
			return "17.00";
		else if ($param == 5)
			return "17.30";
		else if ($param == 6)
			return "18.00";
		else if ($param == 7)
			return "18.30";
		else if ($param == 8)
			return "19.00";
		else if ($param == 9)
			return "19.30";
		else if ($param == 10)
			return "20.00";
		else if ($param == 11)
			return "20.30";
		else if ($param == 12)
			return "21.00";
	}

}

//end of file